package dao;

import entity.Owner;
import util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class OwnerDao {
    public List<Owner> getAllOwners() throws Exception {
        String sql = "SELECT * FROM owner ORDER BY create_time DESC";
        List<Owner> owners = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Owner owner = new Owner();
                owner.setId(rs.getInt("id"));
                owner.setUsername(rs.getString("username"));
                owner.setPassword(rs.getString("password"));
                owner.setName(rs.getString("name"));
                owner.setBuilding(rs.getString("building"));
                owner.setUnit(rs.getString("unit"));
                owner.setRoom(rs.getString("room"));
                owner.setType(rs.getString("type"));
                owner.setPlateNumber(rs.getString("plate_number"));
                owner.setPhone(rs.getString("phone"));
                owner.setCreateTime(rs.getTimestamp("create_time"));
                owners.add(owner);
            }
        }
        return owners;
    }

    public Owner getOwnerById(int id) {
        String sql = "SELECT * FROM owner WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Owner owner = new Owner();
                owner.setId(rs.getInt("id"));
                owner.setUsername(rs.getString("username"));
                owner.setPassword(rs.getString("password"));
                owner.setName(rs.getString("name"));
                owner.setBuilding(rs.getString("building"));
                owner.setUnit(rs.getString("unit"));
                owner.setRoom(rs.getString("room"));
                owner.setType(rs.getString("type"));
                owner.setPlateNumber(rs.getString("plate_number"));
                owner.setPhone(rs.getString("phone"));
                owner.setCreateTime(rs.getTimestamp("create_time"));

                return owner;
            }
        } catch (Exception e) {
            throw new RuntimeException("获取业主信息失败", e);
        }
        return null;
    }

    public void addOwner(Owner owner) {
        String sql = "INSERT INTO owner (username, password, name, building, unit, room, type, plate_number, phone, create_time) "
                +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, owner.getUsername());
            stmt.setString(2, owner.getPassword());
            stmt.setString(3, owner.getName());
            stmt.setString(4, owner.getBuilding());
            stmt.setString(5, owner.getUnit());
            stmt.setString(6, owner.getRoom());
            stmt.setString(7, owner.getType());
            stmt.setString(8, owner.getPlateNumber());
            stmt.setString(9, owner.getPhone());
            stmt.setTimestamp(10, new Timestamp(owner.getCreateTime().getTime()));

            stmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("添加业主失败", e);
        }
    }

    public void updateOwner(Owner owner) {
        System.out.println("DAO: 开始更新业主 ID=" + owner.getId());
        String sql = "UPDATE owner SET username = ?, password = ?, name = ?, building = ?, " +
                "unit = ?, room = ?, type = ?, plate_number = ?, phone = ?, create_time = ? " +
                "WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 打印所有要更新的值
            System.out.println("更新数据：");
            System.out.println("username=" + owner.getUsername());
            System.out.println("name=" + owner.getName());
            System.out.println("room=" + owner.getRoom());
            System.out.println("type=" + owner.getType());
            System.out.println("createTime=" + owner.getCreateTime());

            stmt.setString(1, owner.getUsername());
            stmt.setString(2, owner.getPassword());
            stmt.setString(3, owner.getName());
            stmt.setString(4, owner.getBuilding());
            stmt.setString(5, owner.getUnit());
            stmt.setString(6, owner.getRoom());
            stmt.setString(7, owner.getType());
            stmt.setString(8, owner.getPlateNumber());
            stmt.setString(9, owner.getPhone());
            stmt.setTimestamp(10, new Timestamp(owner.getCreateTime().getTime()));
            stmt.setInt(11, owner.getId());

            int rows = stmt.executeUpdate();
            System.out.println("更新影响的行数：" + rows);

        } catch (Exception e) {
            System.out.println("更新失败：" + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("更新业主信息失败", e);
        }
    }

    public void deleteOwner(int id) {
        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false); // 开启事务
            try {
                // 1. 先删除支付记录
                String deletePaymentSql = "DELETE FROM payment_record WHERE owner_id = ?";
                try (PreparedStatement paymentStmt = conn.prepareStatement(deletePaymentSql)) {
                    paymentStmt.setInt(1, id);
                    paymentStmt.executeUpdate();
                }

                // 2. 删除账户余额记录
                String deleteBalanceSql = "DELETE FROM account_balance WHERE owner_id = ?";
                try (PreparedStatement balanceStmt = conn.prepareStatement(deleteBalanceSql)) {
                    balanceStmt.setInt(1, id);
                    balanceStmt.executeUpdate();
                }

                // 3. 最后删除业主记录
                String deleteOwnerSql = "DELETE FROM owner WHERE id = ?";
                try (PreparedStatement ownerStmt = conn.prepareStatement(deleteOwnerSql)) {
                    ownerStmt.setInt(1, id);
                    ownerStmt.executeUpdate();
                }

                conn.commit(); // 提交事务
            } catch (Exception e) {
                conn.rollback(); // 发生异常时回滚事务
                throw new RuntimeException("删除业主失败", e);
            }
        } catch (Exception e) {
            throw new RuntimeException("删除业主失败", e);
        }
    }

    public void register(Owner owner) throws Exception {
        String sql = "INSERT INTO owner (username, password, name, building, unit, room, type, plate_number, phone) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, owner.getUsername());
            ps.setString(2, owner.getPassword());
            ps.setString(3, owner.getName());
            ps.setString(4, owner.getBuilding());
            ps.setString(5, owner.getUnit());
            ps.setString(6, owner.getRoom());
            ps.setString(7, owner.getType());
            ps.setString(8, owner.getPlateNumber());
            ps.setString(9, owner.getPhone());

            ps.executeUpdate();
        }
    }

    public Owner login(String username, String password) throws Exception {
        String sql = "SELECT * FROM owner WHERE username = ? AND password = ?";

        try (Connection conn = DBUtil.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, username);
            ps.setString(2, password);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Owner owner = new Owner();
                    owner.setId(rs.getInt("id"));
                    owner.setUsername(rs.getString("username"));
                    owner.setPassword(rs.getString("password"));
                    owner.setName(rs.getString("name"));
                    owner.setBuilding(rs.getString("building"));
                    owner.setUnit(rs.getString("unit"));
                    owner.setRoom(rs.getString("room"));
                    owner.setType(rs.getString("type"));
                    owner.setPlateNumber(rs.getString("plate_number"));
                    owner.setPhone(rs.getString("phone"));
                    owner.setCreateTime(rs.getTimestamp("create_time"));
                    return owner;
                }
                return null;
            }
        }
    }
}